package main.java.com.dao.impl;

import main.java.com.config.BaseDao;
import main.java.com.dao.DishDao;
import main.java.com.entity.Category;
import main.java.com.entity.Dish;

import java.sql.*;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

public class DishDaoImpl extends BaseDao implements DishDao {

    // 查询所有菜品并分页
    @Override
    public List<Dish> getAllDishes(int offset, int limit, String keyword, Integer status) {
        List<Dish> dishes = new ArrayList<>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = this.getConnection();

            StringBuilder sql = new StringBuilder("SELECT d.*, c.name AS categoryName FROM dish d JOIN category c ON d.category_id = c.id WHERE 1 = 1");

            // 如果有关键词，则添加关键词搜索条件
            if (keyword != null && !keyword.isEmpty()) {
                sql.append(" AND (d.name LIKE ? OR d.code LIKE ?)");
            }
            // 如果有状态参数，则添加状态筛选条件
            if (status != null) {
                sql.append(" AND d.status =?");
            }
            // 添加排序和分页条件
            sql.append(" ORDER BY d.sort DESC LIMIT ?,?");

            preparedStatement = connection.prepareStatement(sql.toString());

            // 参数索引，用于填充预编译语句的参数
            int index = 1;
            // 如果有关键词，设置关键词搜索参数
            if (keyword != null && !keyword.isEmpty()) {
                preparedStatement.setString(index++, "%" + keyword + "%");
                preparedStatement.setString(index++, "%" + keyword + "%");
            }
            // 如果有状态参数，设置状态筛选参数
            if (status != null) {
                preparedStatement.setInt(index++, status);
            }
            // 设置分页参数
            preparedStatement.setInt(index++, offset);
            preparedStatement.setInt(index++, limit);

            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                Dish dish = new Dish();
                dish.setId(resultSet.getLong("id"));
                dish.setName(resultSet.getString("name"));
                dish.setCategory_id(resultSet.getLong("category_id"));
                dish.setPrice(resultSet.getBigDecimal("price"));
                dish.setCode(resultSet.getString("code"));
                dish.setImage(resultSet.getString("image"));
                dish.setDescription(resultSet.getString("description"));
                dish.setStatus(resultSet.getInt("status"));
                dish.setSort(resultSet.getInt("sort"));
                dish.setCreate_time(resultSet.getDate("create_time"));
                dish.setUpdate_time(resultSet.getDate("update_time"));
                dish.setCreate_user(resultSet.getLong("create_user"));
                dish.setUpdate_user(resultSet.getLong("update_user"));
                dish.setIs_deleted(resultSet.getInt("is_deleted"));
                dish.setCategoryName(resultSet.getString("categoryName"));
                dishes.add(dish);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeResources(connection, preparedStatement, resultSet);
        }
        return dishes;
    }

    @Override
    public List<Dish> getAllDishes2() {
        List<Dish> dishes = new ArrayList<>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = this.getConnection();
            String sql = "select * from dish";
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                Dish dish = new Dish();
                dish.setId(resultSet.getLong("id"));
                dish.setName(resultSet.getString("name"));
                dish.setCategory_id(resultSet.getLong("category_id"));
                dish.setPrice(resultSet.getBigDecimal("price"));
                dish.setCode(resultSet.getString("code"));
                dish.setImage(resultSet.getString("image"));
                dish.setDescription(resultSet.getString("description"));
                dish.setStatus(resultSet.getInt("status"));
                dish.setSort(resultSet.getInt("sort"));
                dish.setCreate_time(resultSet.getDate("create_time"));
                dish.setUpdate_time(resultSet.getDate("update_time"));
                dish.setCreate_user(resultSet.getLong("create_user"));
                dish.setUpdate_user(resultSet.getLong("update_user"));
                dish.setIs_deleted(resultSet.getInt("is_deleted"));
//                dish.setCategoryName(resultSet.getString("categoryName"));
                dishes.add(dish);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeResources(connection, preparedStatement, resultSet);
        }
        return dishes;
    }

    //根据id获取菜品信息
    @Override
    public Dish getDishById(Long id) {
        Dish dish = null;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = this.getConnection();
            String sql = "SELECT d.*, c.name AS categoryName FROM dish d JOIN category c ON d.category_id = c.id WHERE d.id =?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setLong(1, id);
            resultSet = preparedStatement.executeQuery();

            if (resultSet.next()) {
                dish = new Dish();
                dish.setId(resultSet.getLong("id"));
                dish.setName(resultSet.getString("name"));
                dish.setCategory_id(resultSet.getLong("category_id"));
                dish.setPrice(resultSet.getBigDecimal("price"));
                dish.setCode(resultSet.getString("code"));
                dish.setImage(resultSet.getString("image"));
                dish.setDescription(resultSet.getString("description"));
                dish.setStatus(resultSet.getInt("status"));
                dish.setSort(resultSet.getInt("sort"));
                dish.setCreate_time(resultSet.getDate("create_time"));
                dish.setUpdate_time(resultSet.getDate("update_time"));
                dish.setCreate_user(resultSet.getLong("create_user"));
                dish.setUpdate_user(resultSet.getLong("update_user"));
                dish.setIs_deleted(resultSet.getInt("is_deleted"));
                dish.setCategoryName(resultSet.getString("categoryName"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeResources(connection, preparedStatement, resultSet);
        }

        return dish;
    }

    //添加菜品方法
    @Override
    public void addDish(Dish dish) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = getConnection();
            String sql = "INSERT INTO dish (name, category_id, price, code, image, description, status, sort, create_time, update_time, create_user, update_user, is_deleted) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, dish.getName());
            preparedStatement.setLong(2, dish.getCategory_id());
            preparedStatement.setBigDecimal(3, dish.getPrice());
            preparedStatement.setString(4, dish.getCode());
            preparedStatement.setString(5, dish.getImage());
            preparedStatement.setString(6, dish.getDescription());
            preparedStatement.setInt(7, dish.getStatus());
            preparedStatement.setInt(8, dish.getSort());
            preparedStatement.setDate(9, new java.sql.Date(dish.getCreate_time().getTime()));
            preparedStatement.setDate(10, new java.sql.Date(dish.getUpdate_time().getTime()));
            preparedStatement.setLong(11, dish.getCreate_user());
            preparedStatement.setLong(12, dish.getUpdate_user());
            preparedStatement.setInt(13, dish.getIs_deleted());
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeResources(connection, preparedStatement, resultSet);
        }
    }

    // 修改方法
    @Override
    public boolean updateDish(Dish dish) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        boolean success = false;

        try {
            connection = getConnection();
            String sql = "UPDATE dish SET name = ?, category_id = ?, price = ?, image = ?, description = ?, status = ? WHERE id = ?";
            preparedStatement = connection.prepareStatement(sql);
            int index = 1;
            preparedStatement.setString(index++, dish.getName());
            preparedStatement.setLong(index++, dish.getCategory_id());
            preparedStatement.setBigDecimal(index++, dish.getPrice());
            preparedStatement.setString(index++, dish.getImage());
            preparedStatement.setString(index++, dish.getDescription());
            preparedStatement.setInt(index++, dish.getStatus());
            preparedStatement.setLong(index++, dish.getId());

            int rowsAffected = preparedStatement.executeUpdate();
            success = rowsAffected > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeResources(connection, preparedStatement, resultSet);
        }
        return success;
    }
    //删除方法
    @Override
    public int deleteDish(Long id) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        int rowsAffected = 0;

        try {
            connection = this.getConnection();
            String sql = "DELETE FROM dish WHERE id =?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setLong(1, id);
            rowsAffected = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeResources(connection, preparedStatement, null);
        }

        return rowsAffected;
    }

    /**
     * 修改菜肴状态方法，可以禁用或启用菜肴
     * 此方法通过更新数据库中菜肴的状态来实现禁用或启用的功能
     *
     * @param id 要修改的菜肴的ID
     * @param status 新的状态，0表示禁用，1表示启用
     */
    @Override
    public void updateDishStatus(Long id, Integer status) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = getConnection();
            String sql = "UPDATE dish SET status =? WHERE id =?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, status);
            preparedStatement.setLong(2, id);
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeResources(connection, preparedStatement, resultSet);
        }
    }

    /**
     * 根据关键字和状态获取菜品数量
     *
     * @param keyword 搜索关键字，用于模糊匹配菜品名称或代码
     * @param status 菜品状态，用于精确匹配菜品的可用性
     * @return 符合条件的菜品数量
     */
    @Override
    public int getDishCount(String keyword, Integer status) {
        int count = 0;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = getConnection();
            StringBuilder sql = new StringBuilder("SELECT COUNT(*) FROM dish d JOIN category c ON d.category_id = c.id WHERE 1 = 1");

            if (keyword != null && !keyword.isEmpty()) {
                sql.append(" AND (d.name LIKE? OR d.code LIKE?)");
            }
            if (status != null) {
                sql.append(" AND d.status =?");
            }

            preparedStatement = connection.prepareStatement(sql.toString());

            int index = 1;
            if (keyword != null && !keyword.isEmpty()) {
                preparedStatement.setString(index++, "%" + keyword + "%");
                preparedStatement.setString(index++, "%" + keyword + "%");
            }
            if (status != null) {
                preparedStatement.setInt(index++, status);
            }

            resultSet = preparedStatement.executeQuery();

            if (resultSet.next()) {
                count = resultSet.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeResources(connection, preparedStatement, resultSet);
        }

        return count;
    }

    //批量删除方法
    @Override
    public int batchDeleteDishes(List<Long> ids) {
        int result = 0; // 初始化结果变量
        String sql = "DELETE FROM dish WHERE id IN (" + String.join(",", ids.stream().map(Object::toString).toArray(String[]::new)) + ")";
        try (PreparedStatement ps = this.getConnection().prepareStatement(sql)) {
            result = ps.executeUpdate(); // 执行删除操作并设置结果
        } catch (SQLException e) {
            e.printStackTrace(); // 打印异常信息
        }
        return result; // 统一返回结果

    }

    //批量停售和批量启售方法
    @Override
    public int batchUpdateStatus(List<Long> ids, int status) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int result = 0;
        try {
            conn = this.getConnection();
            StringBuilder sql = new StringBuilder("UPDATE dish SET status = ? WHERE id IN (");
            for (int i = 0; i < ids.size(); i++) {
                sql.append("?");
                if (i < ids.size() - 1) {
                    sql.append(",");
                }
            }
            sql.append(")");
            System.out.println("SQL: " + sql.toString()); // 添加日志输出
            pstmt = conn.prepareStatement(sql.toString());

            int index = 1;
            pstmt.setInt(index++, status);
            for (Long id : ids) {
                pstmt.setLong(index++, id);
            }

            result = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.closeAll(conn, pstmt, null);
        }
        return result;
    }


    @Override
    public List<Dish> getDishesByCategory(String categoryName) {
        List<Dish> dishes = new ArrayList<>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = this.getConnection();
            String sql = "SELECT d.*, c.name AS categoryName FROM dish d JOIN category c ON d.category_id = c.id WHERE c.name = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, categoryName);
            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                Dish dish = new Dish();
                dish.setId(resultSet.getLong("id"));
                dish.setName(resultSet.getString("name"));
                dish.setCategory_id(resultSet.getLong("category_id"));
                dish.setPrice(resultSet.getBigDecimal("price"));
                dish.setCode(resultSet.getString("code"));
                dish.setImage(resultSet.getString("image"));
                dish.setDescription(resultSet.getString("description"));
                dish.setStatus(resultSet.getInt("status"));
                dish.setSort(resultSet.getInt("sort"));
                dish.setCreate_time(resultSet.getDate("create_time"));
                dish.setUpdate_time(resultSet.getDate("update_time"));
                dish.setCreate_user(resultSet.getLong("create_user"));
                dish.setUpdate_user(resultSet.getLong("update_user"));
                dish.setIs_deleted(resultSet.getInt("is_deleted"));
                dish.setCategoryName(resultSet.getString("categoryName"));
                dishes.add(dish);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeResources(connection, preparedStatement, resultSet);
        }
        return dishes;
    }



    private void closeResources(Connection connection, PreparedStatement preparedStatement, ResultSet resultSet) {
        try {
            if (resultSet != null) {
                resultSet.close();
            }
            if (preparedStatement != null) {
                preparedStatement.close();
            }
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
